1. Identification of Copyright
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
2. The Field of the Invention
The present invention relates generally to computer-implemented database systems. More specifically, the present invention relates to an apparatus and method for dynamically generating query explain data with a database system.
3. The Relevant Technology
Databases are computerized information storage and retrieval systems. Databases are managed by systems and may take the form of relational databases and hierarchical databases. A Relational Database Management System (RDBMS) is a database system which uses relational techniques for storing and retrieving data. Relational databases are organized into tables consisting of rows (tuples) and columns of data. A relational database typically includes many tables, and each table includes multiple rows and columns. The tables are conventionally stored in direct access storage devices (DASD), such as magnetic or optical disk drives, for semi-permanent storage.
Generally, users communicate with an RDBMS using a Structured Query Language (SQL) interface. The SQL interface allows users to create, manipulate, and query a database by formulating relational operations on the tables, either interactively, in batch files, or embedded in host languages such as C and COBOL. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
The SQL standard provides that each RDBMS should respond to a particular query in the same way, regardless of the underlying database. However, the method that the RDBMS actually uses to find the requested information in the database is left to the RDBMS. Typically, the RDBMS is capable of accessing the requested data in a number of different manners. The RDBMS, therefore, attempts to select the manner that minimizes the computer time and resources (i.e. cost) for executing the query.
When the RDBMS determines how to execute the SQL statements, the set of steps created by the RDBMS for executing the SQL statements is commonly referred to as the xe2x80x9caccess path.xe2x80x9d In other words, the access path is a sequence of operations used by the RDBMS to obtain the data requested by the SQL query. Depending on the access path, an SQL statement might, for instance, search an entire table space, or it might use an index. The access path is a key to determining how well an SQL statement performs. The description of the access path is stored in a table often referred to as a xe2x80x9cplan table,xe2x80x9d which typically stores the access path data for one or more SQL statements.
In addition to determining the access path, many databases estimate the cost (in CPU time in milliseconds or service units) for executing each SQL statement. Often, the estimated costs are stored in a table, referred to in the case of DB2(copyright) for OS/390(copyright) as a xe2x80x9cstatement table.xe2x80x9d Like the plan table, the statement table generally stores the estimated statement costs for one or more SQL statements.
Moreover, some databases store information relating to user-defined functions in a table often referred to as a xe2x80x9cfunction -table.xe2x80x9d User-defined functions can be very useful in developing database applications. Accordingly, it is advantageous to have information relating to the user-defined functions in a single, convenient location.
Collectively, the above-described access path data, statement cost data, and function data are referred to herein as xe2x80x9cexplain data.xe2x80x9d The plan table, statement table, and function table are referred to herein as xe2x80x9cexplain tables.xe2x80x9d Many database systems, such as the RDBMS, provide a query explain program which is used to access the explain tables. The query explain programs are in some instances configured to provide the information, referred to herein as explain data, in a graphical manner, or in a manner otherwise readily comprehendible to a user.
While the explain data is typically generated at bind time, the explain data can also be generated dynamically in response to a user-supplied query statement. A benefit of generating the explain data dynamically, is that the user is allowed to make hypothetical investigations. That is, the user can alter the queries submitted to the database in a hypothetical setting, and quickly see the results in the access data path of changes to queries.
Conventionally, when a user desires explain data for a query, the explain data is requested in each instance from the database system. However, this can result in heavy network traffic and degrade the performance of the database system. Accordingly, what is needed is a system, method, and article of manufacture for locally caching query explain data.
The present invention solves the foregoing problems by providing a system, method, and article of manufacture for locally caching query explain data. In one aspect of the invention, an apparatus for locally caching query execution data received across a network from a database system includes a processor for executing instructions and a memory device having thereon modules of operational data and executable code for execution by the processor.
In one embodiment, the modules include a query explain program configured to operate on a workstation communicating over a network with a server hosting the database system, the query explain program further configured to receive query explain data from the database system over the network. The modules also preferably include a data cache residing on the first computer station and communicating with the query explain program, the data cache executable as an integral component of the query explain program and configured to receive and temporarily store exclusively the query explain data received from the database system over the network.
In one embodiment, a cache clearing module is also provided. Preferably, the cache clearing module is configured to automatically clear a user-selectable portion of data from the data cache when the data cache reaches a selected size.
In certain embodiments, a graphical user interface , (GUI) window accessible through the query explain program may also be provided. Preferably, the GUI window is configured to be displayed on an output device connected to the first computer station and comprises user controls for configuring the data cache. The GUI window may also comprise a manual cache clearing control configured to allow a user to manually clear the data from the data cache. Additionally, a cache size display may be provided and may be configured to display the current amount of data stored in the data cache.
A cache threshold size designation control may also be provided. In one embodiment, the cache threshold size designation control is configured to receive a user designation of the threshold size of the data cache, such that the data is automatically cleared from the data cache when the threshold size is reached.
In another aspect of the invention, a method of locally caching query explain data received across a network from a database system is provided and includes a step of requesting by a query explain program operating on a first computer.station, query explain data from the database system hosted at a second computer station. The method may also comprise receiving the query explain data from the database system over the network and temporarily storing the query explain data received from the database system over the network in a data cache local to the query explain program.
In one embodiment, the second computer station comprises a server, the first computer station comprises a workstation attached to the server, and the data cache is an integral component of the query explain program. The method may also comprise providing within the query explain program a user-enabled control for enabling or disabling the data cache.
The method may also comprise providing a graphical user interface (GUI) window accessible to a user from the query explain program. Preferably, the GUI window is configured to be displayed on an output device connected to the first computer station. The method may also comprise providing user controls within the GUI window for configuring the data cache.
In a further embodiment, providing a GUI window further comprises providing a cache size display within the GUI window. Preferably, the cache size display is configured to display for a user the current amount of data in the data cache. Providing the controls may further comprise providing a manual cache clearing control configured to allow a user to manually clear data from the data cache.
The method may further comprise automatically clearing the data cache when the data cache reaches a selected size. Accordingly, providing the controls may comprise providing a cache threshold size designation control configured to receive a user designation of the selected size of the data cache, such that data from the data cache is automatically cleared when the data cache reaches the selected size. The method may thus comprise receiving a user designation of the selected size and automatically clearing a user-selectable portion of the data cache when the data cache reaches the selected size.
The method may also comprise clearing a user-selectable portion of the data cache when the data cache reaches a selected size. Clearing a user-selectable portion may comprise clearing the least recently used query explain data from the data cache.
In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method of locally caching query explain data received across a network from a database system.
These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth in the following specification.